IF exists(SELECT * FROM dbo.sysobjects WHERE name='WEB_vakrapportVakOverzicht' AND xtype='P') 
BEGIN
	DROP PROCEDURE WEB_vakrapportVakOverzicht
END
GO

CREATE       procedure WEB_vakrapportVakOverzicht
	(@vak varchar(2),
	 @voorstelNr int,
	 @periode varchar(2))
as
set nocount on

select vib.code into #overschot from vakrapportIndividueleRekenwijze as vib
  left join vakrapportOpdrachtCodes as vc
    on vc.code = vib.code 
		and vc.klas=vib.klas 
		and vc.vak = vib.vak 
		and vc.periode = vib.periode
where vib.vak=@vak 
	  and vib.periode=@periode
	    and vc.code is null

select 1 as volgNr,'a' as hulpNr,'<?xml version=''1.0''?>' as tekst
union
select 2,'a','<vakrapport periode="' + @periode + '" vak="' + @vak + '">'
union
select 3,'a','<vakken><' + @vak + ' leraar="de leraar">'
union
select distinct 4,code, '<' + lower(code) + '>' + code + '*</' + lower(code) + '>' 
  from vakrapportXSLCompositie as vxsl
    left join vakrapportopdrachtcodes as voc
      on voc.klas = vxsl.klas
	and voc.periode = vxsl.periode
          and voc.vak = vxsl.vak
where vxsl.vak=@vak 
	and vxsl.periode=@periode 
	  and vxsl.voorstelNr = @voorstelNr
union 
select distinct 4,code, '<' + lower(code) + 'com>' + code + ' - detail </' + lower(code) + 'com>' from vakrapportopdrachtcodes
where vak=@vak and periode=@periode
union 
select distinct 4,code,'<' + lower(code) + '>' + code + '</' + lower(code) + '>' from #overschot
union
select distinct 4,code + 'gem','<' + lower(code) + 'gem>' + code + 'gem</' + lower(code) + 'gem>' from vakrapportCollectieveRekenwijze
where vak=@vak and periode=@periode
union
select 5,'a','</' + @vak + '></vakken></vakrapport>'
order by volgNr,hulpnr